const connection = require("../app/database");

class YpyAsk {
  async create(content, user_id, user_name, love_num, avatar, category_id) {
    const staetment =
      "INSERT INTO yipianyue_ask (content, user_id,user_name,love_num,avatar,category_id) VALUES (?,?,?,?,?,?);";
    const result = await connection.execute(staetment, [
      content,
      user_id,
      user_name,
      love_num,
      avatar,
      category_id,
    ]);
    return result[0];
  }
  async addComment(
    article_id,
    user_id,
    comment_id,
    content,
    avatar,
    user_name
  ) {
    //改变评论数据
    let staetment0 = `UPDATE yipianyue_ask SET comment_num=comment_num+1
    WHERE id=?;`;
    await connection.execute(staetment0, [article_id]);

    const staetment =
      "INSERT INTO yipianyue_ask_comment (article_id,user_id,comment_id,content,avatar,user_name) VALUES (?,?,?,?,?,?);";
    const result = await connection.execute(staetment, [
      article_id,
      user_id,
      comment_id,
      content,
      avatar,
      user_name,
    ]);
    return result[0];
  }

  async list(page, size) {
    const staetment = `SELECT ya.id id,ya.content content, ya.user_id uid,ya.user_name uname,ya.avatar avatar,ya.love_num love_num,ya.hate_num hate_num,
        ya.createAt createAt,(
        SELECT COUNT(*) from yipianyue_ask_comment yacc WHERE yacc.article_id=ya.id
        ) comment_num
        from yipianyue_ask ya
        LEFT JOIN yipianyue_ask_comment yac ON yac.article_id=ya.id
        GROUP BY ya.id
        LIMIT ?,?;`;
    const result = await connection.execute(staetment, [page, size]);
    return result[0];
  }

  async commentList(article_id, page, size) {
    const staetment = `SELECT *
        FROM yipianyue_ask_comment
        WHERE article_id=?
        LIMIT ?,?;`;
    const result = await connection.execute(staetment, [
      article_id,
      page,
      size,
    ]);
    return result[0];
  }

  async like(article_id, islike, user_id) {
    console.log(article_id, islike, user_id);
    var staetment0 = `
        SELECT * from yipianyue_ask_like
        WHERE article_id=?  AND is_like=? AND user_id=?;
        `;
    const result0 = await connection.execute(staetment0, [
      article_id,
      islike,
      user_id,
    ]);
    let staetment2;
    if (result0[0].length > 0) {
      // 删除动态表中的点赞记录
      let staetment3 = `DELETE FROM yipianyue_ask_like WHERE article_id=? AND is_like=? AND user_id=?;`;
      await connection.execute(staetment3, [article_id, islike, user_id]);
      if (islike == "true") {
        staetment2 = `UPDATE yipianyue_ask set love_num=love_num-1 WHERE id=?;`;
      } else {
        staetment2 = `UPDATE yipianyue_ask set hate_num=hate_num-1 WHERE id=?;`;
      }
      const result = await connection.execute(staetment2, [article_id]);
      return {
        msg: "已取消点赞或点踩!",
        code: "7000",
      };
    }

    // 给动态表中添加点赞记录
    let staetment1 = `INSERT INTO yipianyue_ask_like(article_id,is_like,user_id) VALUES (?,?,?);`;
    await connection.execute(staetment1, [article_id, islike, user_id]);

    if (islike == "true") {
      staetment2 = `UPDATE yipianyue_ask set love_num=love_num+1 WHERE id=?;`;
    } else {
      staetment2 = `UPDATE yipianyue_ask set hate_num=hate_num+1 WHERE id=?;`;
    }

    const result = await connection.execute(staetment2, [article_id]);
    return result[0];
  }

  async commentLke(comment_id, islike, article_id, user_id) {
    var staetment0 = `
    SELECT * from yipianyue_ask_comment_like
    WHERE article_id=?  AND is_like=? AND user_id=? AND comment_id=?;
    `;
    const result0 = await connection.execute(staetment0, [
      article_id,
      islike,
      user_id,
      comment_id,
    ]);
    let staetment2;
    if (result0[0].length > 0) {
      // 删除动态表中的点赞记录
      let staetment3 = `DELETE FROM yipianyue_ask_comment_like WHERE article_id=? AND is_like=? AND user_id=? AND comment_id=?;`;
      await connection.execute(staetment3, [
        article_id,
        islike,
        user_id,
        comment_id,
      ]);
      if (islike == "true") {
        staetment2 = `UPDATE yipianyue_ask_comment set love_num=love_num-1 WHERE id=?;`;
      } else {
        staetment2 = `UPDATE yipianyue_ask_comment set hate_num=hate_num-1 WHERE id=?;`;
      }
      const result = await connection.execute(staetment2, [article_id]);
      return {
        msg: "已取消点赞或点踩!",
        code: "7000",
      };
    }

    // 给动态表中添加点赞记录
    let staetment1 = `INSERT INTO yipianyue_ask_comment_like(article_id,is_like,user_id,comment_id) VALUES (?,?,?,?);`;
    await connection.execute(staetment1, [
      article_id,
      islike,
      user_id,
      comment_id,
    ]);

    if (islike == "true") {
      staetment2 = `UPDATE yipianyue_ask_comment set love_num=love_num+1 WHERE id=?;`;
    } else {
      staetment2 = `UPDATE yipianyue_ask_comment set hate_num=hate_num+1 WHERE id=?;`;
    }

    const result = await connection.execute(staetment2, [comment_id]);
    return result[0];
  }

  async popular(ctx,next) {
    const staetment =`SELECT * FROM yipianyue_ask ya ORDER BY
    ya.love_num DESC, ya.comment_num DESC
   LIMIT 30;`
    const result = await connection.execute(staetment, []);
    return result[0];
  }

  async search (keywords){
    const staetment =`select * from yipianyue_ask where content REGEXP "[ ${keywords} ]";`;
    const result = await connection.execute(staetment, []);
    return result[0];
  }
}

module.exports = new YpyAsk();
